from sqlalchemy import create_engine, Column, Integer, String,DateTime,Numeric
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import JSON
from sqlalchemy.sql import func
# from sqlalchemy.engine.url import URL
# import settings

Base = declarative_base()

def to_dict(self):
    return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}

Base.to_dict = to_dict
 
def db_connect():
    # return create_engine(URL(**settings.DATABASE))
    return create_engine('mysql+pymysql://amazon:Amazon123$%^@localhost:3306/amazon?charset=utf8')  #定义一个指向sqlalchemy.db数据库的引擎
 
def create_amazon_table(engine):
    Base.metadata.create_all(engine)      #创建数据库
 
class Catelog(Base):
    __tablename__ = 'catelog'     #表的名字
    id = Column(String(64), primary_key = True)
    cate_line = Column(JSON) # 目录结构 如：['sport','t-shirt','men']
    cate_top = Column(String(64)) # 当前主分类 如： ‘sport’
    cate_name = Column(String(64)) # 当前目录名称 如： 'sport > t-shirt > men''
    cate_level = Column(Integer) # 当前目录层次 如： 3
    bestsellers_url = Column(String(512)) # 最好买的url
    newreleases_url = Column(String(512)) # 最新的url
    movers_url = Column(String(512)) # 增长最快的url
    created_at = Column(DateTime,server_default=func.now())
    updated_at = Column(DateTime,server_default=func.now(),onupdate=func.now())
    crawl_auto = Column(Integer,default=1)

class Product(Base):
    __tablename__ = 'product'
    id = Column(String(64), primary_key = True)
    product_name = Column(String(250))
    # 产品url sha1码为ID
    product_url = Column(String(512))
    # 商品图片
    product_img = Column(String(512))
    # 商品价格
    price_amazon = Column(Numeric(10, 2))
    product_price = Column(Numeric(10, 2))
    # 产品评论数
    product_reviews = Column(Integer)
    # 产品评价
    product_rating = Column(Numeric(2, 1))
    # 商品主分类，多个为数组 如：【‘sport’，‘clothing’】
    cate_top = Column(String(64))
    # 商品分类，多个为数组 如：['sport','sport > t-shirt > men']
    cate_ref = Column(JSON)
    # 月度销售金额
    month_sales = Column(Integer)
    # 销售排名 多个为数组，如：[{catelog:'sport',rank:'1'},{'catelog':'sport > t-shirt > men',rank:'2'}]
    bestsellers_rank = Column(JSON)
    rank_top = Column(Integer)
    # 新品排名,rank数组 如： [{catelog:'sport',rank:'1'},{'catelog':'sport > t-shirt > men',rank:'2'}]
    newreleases_rank = Column(JSON)
    # 增长排名,rank数组 如： [{catelog:'sport',rank:'1'},{'catelog':'sport > t-shirt > men',rank:'2'}]
    movers_rank = Column(JSON)
    created_at = Column(DateTime,server_default=func.now())
    updated_at = Column(DateTime,server_default=func.now(),onupdate=func.now())